"""
-*- coding:utf-8 -*-
__project__ = 'le-bo16'
__file_name__ = 'rickey'
__author__ = 'RickeyTing'
__time__ = '2020/10/27 19:36'
__product_name = PyCharm
# code is far away from bugs with the god animal protecting
    I love animals. They taste delicious.
              ┏┓      ┏┓
            ┏┛┻━━━┛┻┓
            ┃        ┃
            ┃  ┳┛  ┗┳  ┃
            ┃      ┻      ┃
            ┗━┓      ┏━┛
                ┃      ┗━━━┓
                ┃  神兽保佑    ┣┓
                ┃　永无BUG！   ┏┛
                ┗┓┓┏━┳┓┏┛
                  ┃┫┫  ┃┫┫
                  ┗┻┛  ┗┻┛
"""

import pymysql
from db import date_base

#查询数据库中库表有无数据

coon = pymysql.connect('49.233.39.160', 'user', 'leboAa!#$123', 'lebo16', 3306)
cur = coon.cursor(cursor=pymysql.cursors.DictCursor)
num = input('请输入数字：')
sql = "select * from students where studentNo = '%s'" % num
print(sql)

#登录、注册、退出数据库
choose = int(input('登录请选择1，注册选择2，退出请选择3'))
if choose == 1:
    print('开始登录')
    UserId = input('请输入姓名：')
    passWd = input('请输入密码：')
    sql1 = "select * from students where name =%s and studentNo=%s;"
    print(sql1)
    cur.execute(sql1, [UserId, passWd])
    print(sql1)

elif choose == 2:
    print('开始注册！')
    UserId = input('请输入姓名：')
    passWd = input('请输入密码：')
    sql2 = "select * from students where name =%s and studentNo=%s;"
    print(sql2)
    count = cur.execute(sql2, [UserId, passWd])
    print(sql2)
    select_01 = cur.fetchone()
    print(select_01)
    if count == 1:
        print('该用户已存在！')
    else:
        sql2 = 'insert into students(studentNo, name) values (%s, %s)'
        cur.execute(sql2, [UserId, passWd])
        coon.commit()
        print('注册成功！')

    if count == 1:
        print('登录成功')
    else:
        print('错误的用户')

elif choose == 3:
    print('退出！')


cur.execute(sql)
select_1 = cur.fetchone()
print(select_1)
cur.close()
coon.close()


#调用数据库，二次封装
class DateBase(object):
    def __init__(self, host = date_base['host'],
                       port = date_base['port'],
                       user = date_base['user'],
                       passwd = date_base['password'],
                       database = date_base['databse']):
        self.host = host
        self.port = port
        self.user = user
        self.passwd = passwd
        self.database = database

    def __get_connection(self):
        if not self.database:
            raise RuntimeError('配置有问题！')
        try:
            self.conn = pymysql.connect(host = self.host,
                                        port = self.port,
                                        user = self.user,
                                        passwd = self.passwd,
                                        database = self.database)
        except:
            raise RuntimeError('初始数据库失败！')
        self.cursor = self.conn.cursor()
        return self.cursor
    def excute_sql(self,sql, params=None):
        self.__get_connection()
        self.cursor.execute(sql,params)
        date_base = self.cursor.fetchone()
        return date_base


if __name__ == '__main__':
    date_base = DateBase(host = date_base['host'],
                       port = date_base['port'],
                       user = date_base['user'],
                       passwd = date_base['password'],
                       database = date_base['databse'])
    data = date_base.excute_sql('select * from students')
    print(data)

    coon.close()









